package com.loveyukari.dao.impl;

import com.loveyukari.dao.RoomLiwuDao;
import com.loveyukari.model.RoomLiwu;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

@Repository
public class JdbcRoomLiwuDao extends BaseDao implements RoomLiwuDao {


    private static final String INSERT_ROOM_LIWU_SQL = "insert into room_liwu (user_id,liwu_id,total,give_date,room_id) values(?,?,?,?,?) ";

    @Override
    public void insertRoomLiwu(RoomLiwu roomLiwu) {
        this.getJdbcTemplate().update(INSERT_ROOM_LIWU_SQL,roomLiwu.getUserId(),roomLiwu.getLiwuId(),roomLiwu.getTotal(),roomLiwu.getGiveDate(),roomLiwu.getRoomId());
    }


    @Override
    public void batchInsertRoomLiwu(List<RoomLiwu> roomLiwuList){
        this.getJdbcTemplate().batchUpdate(INSERT_ROOM_LIWU_SQL, roomLiwuList, roomLiwuList.size(), new ParameterizedPreparedStatementSetter<RoomLiwu>() {
            @Override
            public void setValues(PreparedStatement ps, RoomLiwu argument) throws SQLException {
                ps.setInt(1,argument.getUserId());
                ps.setInt(2,argument.getLiwuId());
                ps.setInt(3,argument.getTotal());
                ps.setDate(4, new Date(argument.getGiveDate().getTime()));
                ps.setInt(5,argument.getRoomId());
            }
        });
    }


    private static final String SELECT_USER_ID_SQL = "select user_id from room_liwu where room_id = ? and give_date = ? and liwu_id = ? and total >= ?";
    @Override
    public List<Integer> selectUserIdByRoomLiwuDateHits(int roomId, java.util.Date giveDate, int liwuId, int hits) {
        List<Integer> userIds = this.getJdbcTemplate().queryForList(SELECT_USER_ID_SQL, Integer.class, roomId,giveDate, liwuId, hits);
        return userIds;
    }


    private static final String SELECT_USER_ID_TOTAL_MAP_SQL = "select user_id,total from room_liwu where room_id = ? and give_date = ? and liwu_id = ? and total >= ?";
    @Override
    public Map<Integer, Integer> selectUserIdByRoomLiwuDateHitsMap(int roomId, java.util.Date giveDate, int liwuId, int hits) {
        Map<Integer, Integer> map = this.getJdbcTemplate().query(SELECT_USER_ID_TOTAL_MAP_SQL, new ResultSetExtractor<Map<Integer, Integer>>() {
            @Override
            public Map<Integer, Integer> extractData(ResultSet rs) throws SQLException, DataAccessException {
                TreeMap<Integer, Integer> map = new TreeMap<Integer, Integer>();
                while (rs.next()) {
                    map.put(rs.getInt("user_id"), rs.getInt("total"));
                }
                return map;
            }
        }, roomId, giveDate, liwuId, hits);
        return map;
    }

    @Override
    public int sumLiwuTotal(int roomId,int gfid, java.util.Date date) {
        List<Integer> list = this.getJdbcTemplate().queryForList(SELECT_SUM_TOTAL_BY_GFID_AND_GIVE_DATE_SQL, Integer.class, roomId, gfid, date);
        if (list.isEmpty()){
            return 0;
        } else {
            return list.get(0);
        }
    }

    private static final String SELECT_SUM_TOTAL_BY_GFID_AND_GIVE_DATE_SQL = "select sum(total) from room_liwu where room_id = ? and liwu_id = ? and give_date = ?";

}
